import java.io.BufferedWriter;
import java.io.FileReader;
import java.io.FileWriter;
import java.sql.*;

import javax.sql.*;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Properties;
import java.util.Scanner;

public class DatabaseConnector {
	
	private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	private static final String DB_URL = "jdbc:mysql://songrecommender.csmwbjcoq6lu.us-east-1.rds.amazonaws.com/song_recommendation";
		///"jdbc:mysql:song_recommendation@songrecommender.csmwbjcoq6lu.us-east-1.rds.amazonaws.com"; //"jdbc:mysql://localhost/song_recommendation";
	
	static final String USER = "song_user";
	static final String PASS = "songsong";	
		
	private Connection conn;
	private Statement stmt;
	
    public DatabaseConnector()
    {
    	conn = null;
    	stmt = null;
    	
    	//DB Connect
    	try
    	{
			Class.forName(JDBC_DRIVER);
			
			conn = DriverManager.getConnection(DB_URL, USER, PASS);
			stmt = conn.createStatement();

		} catch(Exception e) {
			e.printStackTrace();
		}
    }
    
    public void createTables()
    {
    	String sql = null;
    	try
    	{
        	sql = "CREATE TABLE IF NOT EXISTS `Song` ( " +
			  "`SongID` varchar(20) NOT NULL, " +
			  "`Name` varchar(50) DEFAULT NULL, " +
			  "`Artist` varchar(50) DEFAULT NULL, " +
			  "`Tempo` float DEFAULT NULL, " +
			  "`Duration` float DEFAULT NULL, " +
			  "`Loudness` float DEFAULT NULL, " +
			  "`Key` float DEFAULT NULL, " +
			  "`Mode` float DEFAULT NULL, " +
			  "`Year` float DEFAULT NULL, " +
			  "`Hotness` float DEFAULT NULL, " +
			  "`Cluster` tinyint(4) NOT NULL DEFAULT '4', " +
			  "`SongIndex` int(11) NOT NULL DEFAULT '0', " +
			  "`Phero0` float NOT NULL DEFAULT '0', " +
			  "`Phero1` float DEFAULT '0', " +
			  "`Phero2` float DEFAULT '0', " +
			  "`Phero3` float DEFAULT '0', " +
			  "`Phero4` float DEFAULT '0', " +
			  "`Phero5` float DEFAULT '0', " +
			  "`Phero6` float DEFAULT '0', " +
			  "`Phero7` float DEFAULT '0', " +
			  "`Phero8` float DEFAULT '0', " +
			  "`Phero9` float DEFAULT '0', " +
			  "PRIMARY KEY (`SongID`), " +
			  "KEY `Cluster` (`Cluster`), " +
			  "KEY `Phero1` (`Phero1`), " +
			  "KEY `Phero2` (`Phero2`), " +
			  "KEY `Phero3` (`Phero3`), " +
			  "KEY `Phero4` (`Phero4`), " +
			  "KEY `Phero5` (`Phero5`), " +
			  "KEY `Phero6` (`Phero6`), " +
			  "KEY `Phero7` (`Phero7`), " +
			  "KEY `Phero8` (`Phero8`), " +
			  "KEY `Phero9` (`Phero9`) " +
			") ENGINE=InnoDB DEFAULT CHARSET=latin1 ";
			
			stmt.execute(sql);
		
		} catch(SQLException se) {
			System.out.println( sql );
			se.printStackTrace();
		}   	

    }
    
    public int getCluster( String songID )
    {
    	String sql = null;
    	try
    	{
			sql = "SELECT `Cluster` FROM `song_recommendation`.`Song` WHERE `SongID` = '" + songID +"'";
			ResultSet rs = stmt.executeQuery(sql);
			
			if (rs.next()) 
			{
				int clust = Integer.parseInt( rs.getString("Cluster") );
				rs.close();
				return clust;
			}
		
		} catch(SQLException se) {
			System.out.println("Cluster**************");
			System.out.println( sql );
			se.printStackTrace();
		}
    	return -1;
    }
    
    public ArrayList<Double> getPheromones( String songID )
    {
    	String sql = null;
    	try
    	{
    		String[] pheroNames = { "Phero0", "Phero1", "Phero2", "Phero3",
    								"Phero4", "Phero5", "Phero6", "Phero7", 
    								"Phero8", "Phero9" };
    		
			sql =  "SELECT" ;
			sql += String.format(" `%s`", pheroNames[0]);			
			for (int i=1; i< 10; i++)
			{
				sql += String.format(", `%s`", pheroNames[i]);
			}
			sql += "FROM `song_recommendation`.`Song` WHERE `SongID` = '" + songID + "'";
			ResultSet rs = stmt.executeQuery(sql);
			
			if (rs.next()) 
			{
				ArrayList<Double> phero = new ArrayList<Double>();
				
				for (int i=0; i< 10; i++)
				{
					
					phero.add( Double.parseDouble( rs.getString(pheroNames[i]) ));
				}
				rs.close();
				return phero;
			}
			
		} catch(SQLException se) {
			System.out.println("PHEROMONE||||||||||||||||");
			System.out.println( sql );
			se.printStackTrace();
		}
    	return null;
    }
    
    public void setPheromones( String songID, ArrayList<Double> pheros )
    {
    	String sql = null;
    	try
    	{
    		String[] pheroNames = { "Phero0", "Phero1", "Phero2", "Phero3",
    								"Phero4", "Phero5", "Phero6", "Phero7", 
    								"Phero8", "Phero9" };
    		
			sql =  "UPDATE `song_recommendation`.`Song` SET" ;
			
			sql += String.format(" `%s`='%f'", pheroNames[0], pheros.get(0));
			for (int i=1; i< pheros.size(); i++)
			{
				sql += String.format(", `%s`='%f'", pheroNames[i], pheros.get(i));
			}
			sql += " WHERE `SongID` = '" + songID + "'";
			stmt.executeUpdate(sql);
		
		} catch(SQLException se) {
			System.out.println(sql);
			se.printStackTrace();
		}
    }
    
    public void clearCluster()
    {
    	try
    	{
			String sql;
			sql = "UPDATE `song_recommendation`.`Song` SET `Cluster` = '0' WHERE 1";
			
			stmt.executeUpdate(sql);
		
		} catch(SQLException se) {
			se.printStackTrace();
		}
    }
    
    public void clearPheromones()
    {
    	try
    	{
			String sql;
			sql = "UPDATE `song_recommendation`.`Song` SET";
			sql += String.format( " `Phero0` = '0'" );
			for (int i = 1; i< 10; i++)
			{
				sql += String.format( ", `Phero%d` = '0'", i);
			}
			sql += "WHERE 1";
			
			stmt.executeUpdate(sql);
		
		} catch(SQLException se) {
			se.printStackTrace();
		}
    }
    
    public void setSongIndex( String songID, String index )
    {
    	try
    	{
			String sql;
			sql = "UPDATE `song_recommendation`.`Song` SET";
			sql += String.format( "`SongIndex` = '%s' WHERE `SongID` = '%s'", index, songID );
			
			stmt.executeUpdate(sql);
		
		} catch(SQLException se) {
			se.printStackTrace();
		}
    }
    
    public ArrayList<RecommendedSong> getMaxScoringSongs( User listener, int bestCluster, boolean fileOut )
    {
    	//not bothering to track songs listened to
    	String sql = null;
    	try
    	{
    		String[] pheroNames = { "Phero0", "Phero1", "Phero2", "Phero3",
    								"Phero4", "Phero5", "Phero6", "Phero7", 
    								"Phero8", "Phero9" };
    		
    		sql =  "SELECT * ";
			sql += "FROM `song_recommendation`.`Song` ";
			
			//TODO add
			sql += "WHERE `SongIndex` != '0' ";
			
			sql += "ORDER BY `" + pheroNames[bestCluster] +"` DESC LIMIT 2000 ";
			ResultSet rs = stmt.executeQuery(sql);
			
			ArrayList<RecommendedSong> allSongs = new ArrayList<RecommendedSong>();
			while (rs.next()) 
			{
				int sIndex = Integer.parseInt( rs.getString("SongIndex") );
				double sum = 0;
				String title = rs.getString("Name");
				String id = rs.getString("SongID");
				double[] sPhero = new double[10];
				sPhero[0] = rs.getFloat("Phero0");
				sPhero[1] = rs.getFloat("Phero1");
				sPhero[2] = rs.getFloat("Phero2");
				sPhero[3] = rs.getFloat("Phero3");
				sPhero[4] = rs.getFloat("Phero4");
				sPhero[5] = rs.getFloat("Phero5");
				sPhero[6] = rs.getFloat("Phero6");
				sPhero[7] = rs.getFloat("Phero7");
				sPhero[8] = rs.getFloat("Phero8");
				sPhero[9] = rs.getFloat("Phero9");
				
				for (int i=0; i< 10; i++)
				{
					double ph = Double.parseDouble( rs.getString(pheroNames[i]) );
					sum += ph * listener.preferences[i];
				}
				
				//allSongs.add( new RecommendedSong(sIndex, sum ));
				allSongs.add( new RecommendedSong(sIndex, sum, id, title, sPhero ));
			}
			rs.close();
			
			Collections.sort(allSongs);
			
			//NEW
			if ( fileOut )
			{
	        	FileWriter outFile = new FileWriter("Records/" + listener.name + ".csv");
	    		BufferedWriter out = new BufferedWriter(outFile);
	    		
	    		out.write( "USER:,," );
	    		for (int i =0; i< 10; i++)
	    		{
	    			out.write(String.format("%f, ", listener.preferences[i]) );
	    		}
	    		out.write("\n");
	    		
	    		System.out.println("REC Count: " + allSongs.size());
	    		
	    		for (int i =0; i<  Math.min(50, allSongs.size()) ; i++)
	    		{
	    			out.write( String.format("%s,%s", allSongs.get(i).name, allSongs.get(i).id) );
	    			RecommendedSong current = allSongs.get(i);
	    			for (int j=0; j<10; j++)
	    			{
	    				out.write( String.format(",%f", current.phero[j]) );
	    				
	    			}
	    			out.write( String.format( ",%f\n", current.userPref ) );
	    		}
				out.close();
			}
			
			return allSongs;
		
		} catch(SQLException se) {
			se.printStackTrace();
			System.out.println(sql);
		} catch (Exception e)
		{
			e.printStackTrace();
			
		}
    	return null;
    }
    
    public Connection getConnection() {
    	return conn;
    }
    
    public Statement getStatement() {
    	return stmt;
    }
    
    public void close() 
    {
        try {
          if (stmt != null) {
        	  stmt.close();
          }

          if (conn != null) {
        	  conn.close();
          }
        } catch (Exception e) {

        }
      }
    
}
